﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Data.Odbc;


namespace Mediatheque
{
    class Connexion
    {
        private const string DATABASENAME = "bdd_mediatheque";
        private OdbcConnection m_conn;
        private Utile outil = new Utile();

        public Connexion() { Start();  }

        public void Start()
        {

            string MyConstring = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost; PORT=3306;DATABASE=" + DATABASENAME + "; USER=root; PASSWORD=; OPTION=0;";
            m_conn = new OdbcConnection(MyConstring);
          //  m_conn.ConnectionTimeout = 1;
            try
            {
                m_conn.Open();
                System.Console.WriteLine("connected");
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Erreur de connexion à la base " + DATABASENAME + " : " + ex.Message);

            }

        }

        public void Update(string table, string champ, string value, string contrainte)
        {
            string strRequete = "UPDATE " + table + " SET " + champ + "='" + value + "' WHERE " + contrainte + "";

            OdbcCommand commande = new OdbcCommand(strRequete, m_conn);
           // Console.WriteLine(strRequete);
            commande.ExecuteNonQuery();
        }

        public void DeleteByID(string table, string id, string value)
        {
            string strRequete = "DELETE FROM " + table + " WHERE " + id + "=" + value + "";
            Console.WriteLine(strRequete);
            OdbcCommand commande = new OdbcCommand(strRequete, m_conn);
            commande.ExecuteNonQuery();
        }

        public string SearchById(string table, string id, string value)
        {
            string strRequete = "SELECT * FROM " + table + " WHERE " + id + "=" + value + "";
            string t = "";
            Console.WriteLine(strRequete);
             OdbcCommand commande = new OdbcCommand(strRequete, m_conn);

                OdbcDataReader resultat = commande.ExecuteReader();
                // On teste s'il existe un résultat
                if (resultat.Read())
                {
                    // Nombre de colonnes
                    int nbColonnes = resultat.FieldCount;
                   
                        
                        for (int i = 0; i < nbColonnes; i++)
                        {
                            //"[" + resultat.GetName(i) + "] : "
                            if (i == nbColonnes - 1)
                               t+= FormatField(resultat, i) + "\n";
                            else
                               t+= FormatField(resultat, i) + " ";
                        }
                       
                     
                  
                resultat.Close();
            }
            return t;

        }

        
        public void Insert(string table, List<String> champs, List<String> values, bool iscontenu)
        {
            // si on ajoute un contenu spécifique, comme un livre ou une carte, on insére la référence du dernier contenu enregistré.

            if (!iscontenu && values.Count > 0)
                values[0] = rechercheDerniereReference("reference", "contenu");


            System.Console.WriteLine(champs.Count + " " +  values.Count);
            // on vérifit d'abord la taille des listes
            if (champs.Count == values.Count)
            {
                // on transforme les listes en chaine de caractère, chaque item de la liste représente un champ 
                string str_champ = "";
                for (int i = 0; i < champs.Count; ++i)
                {
                    if (i < (champs.Count - 1))
                        str_champ += champs[i] + ",";
                    else
                        str_champ += champs[i];
                }

                string str_value = "";
                for (int i = 0; i < values.Count; ++i)
                {
                    if (i < (values.Count - 1))
                        str_value += values[i] + ",";
                    else
                        str_value += values[i];
                }

                string strRequete = "INSERT INTO " + table + " (" + str_champ + ")" + " VALUE (" + str_value + ")";

                OdbcCommand commande = new OdbcCommand(strRequete, m_conn);
                   System.Console.WriteLine(strRequete);
               commande.ExecuteNonQuery();
            }

        }

        public List<String> rechercheGlobale(string table, List<string> champ, List<string> value, int debut, int fin)
        {
            List<string> tmp = new List<string>();
            if (champ.Count>0 && value.Count>0)
            {
                string strRequete = "SELECT * FROM " + table + " WHERE " + champ[0] + " LIKE '" + value[0] + "%'";
                for (int i = 1; i < champ.Count; i++)
                    strRequete += " AND " + champ[i] + " LIKE '" + value[i] + "%'";
                Console.WriteLine(strRequete);
                //strRequete = "SELECT * FROM " + table + " WHERE " + champ + " LIKE '%" + value + "%'";
                
                OdbcCommand commande = new OdbcCommand(strRequete, m_conn);

                OdbcDataReader resultat = commande.ExecuteReader();
                // On teste s'il existe un résultat
                if (resultat.Read())
                {
                    // Nombre de colonnes
                    int nbColonnes = resultat.FieldCount;
                    do
                    {
                        // Parcours des différentes colonnes
                        string t = "";
                        for (int i = debut; i < fin; i++)
                        {
                            //"[" + resultat.GetName(i) + "] : "
                            if (i == nbColonnes - 1)
                               t+= FormatField(resultat, i) + "\n";
                            else
                               t+= FormatField(resultat, i) + " ";
                        }
                        tmp.Add(t);
                        t = "";
                    } while (resultat.Read());
                }

                resultat.Close();
            }
            return tmp;
        }

        public string rechercheDerniereReference(string id, string table)
        {
            string strRequete = "SELECT " + id + " FROM " + table + " ORDER BY " + id + " DESC";
            string tmp = "";

            OdbcCommand commande = new OdbcCommand(strRequete, m_conn);

            OdbcDataReader resultat = commande.ExecuteReader();

            if (resultat.Read())
                tmp = FormatField(resultat, 0);

            resultat.Close();

            return tmp;
        }



        // Retourne une chaine de caractère représantant la colonne <i> de <resultat> //
        protected string FormatField(OdbcDataReader resultat, int i)
        {
            // Champs null
            if (resultat.IsDBNull(i))
                return "[NULL]";

            // Le type du champs
            Type tField = resultat.GetFieldType(i);

            // String
            if (tField == Type.GetType("System.String"))
                return resultat.GetString(i);

            // Entier 32 bits
            if (tField == Type.GetType("System.Int32"))
                return resultat.GetInt32(i).ToString();

            // Entier 64 bits
            if (tField == Type.GetType("System.Int64"))
                return resultat.GetInt64(i).ToString("00000000");

            // Inconnu
            return "[Type: " + resultat.GetFieldType(i) + "]";
        }

        public void insertContenu(string typeContenu, Contenu contenu, Contenu specifique, List<string> generale, List<string> infos)
        {
            System.Console.WriteLine(outil.listOfStringToString(contenu.Champs));
            System.Console.WriteLine(outil.listOfStringToString(specifique.Champs));

            this.Insert("contenu", contenu.Champs, generale, true);

            this.Insert(typeContenu, specifique.Champs, infos, false);

            // co.Insert("livre", livre1.Champs, l2);

        }

        public List<String> SelectionGlobale(string table,string tri, int debut, int fin)
        {
            List<string> tmp = new List<string>();
            string strRequete = "SELECT * FROM " + table+" ORDER BY "+tri ;
                

                OdbcCommand commande = new OdbcCommand(strRequete, m_conn);

                OdbcDataReader resultat = commande.ExecuteReader();
                // On teste s'il existe un résultat
                if (resultat.Read())
                {
                    // Nombre de colonnes
                    int nbColonnes = resultat.FieldCount;
                    do
                    {
                        // Parcours des différentes colonnes
                        string t = "";
                        for (int i = debut; i < fin; i++)
                        {
                            //"[" + resultat.GetName(i) + "] : "
                            if (i == nbColonnes - 1)
                                t += FormatField(resultat, i) + "\n";
                            else
                                t += FormatField(resultat, i) + " ";
                        }
                        tmp.Add(t);
                        t = "";
                    } while (resultat.Read());
                }

                resultat.Close();
            
            return tmp;
        }

        public List<String[]> rechercheGlobaleList(string table, string tri, int fin)
        {
            List<string[]> tmp = new List<string[]>();
          
                string strRequete = "SELECT * FROM " + table + " ORDER BY " + tri;

               
                OdbcCommand commande = new OdbcCommand(strRequete, m_conn);

                OdbcDataReader resultat = commande.ExecuteReader();
                // On teste s'il existe un résultat
                if (resultat.Read())
                {
                    // Nombre de colonnes
                    int nbColonnes = resultat.FieldCount;
                    do
                    {
                        // Parcours des différentes colonnes
                        string[] t = new string[fin];
                        for (int i = 0; i < fin; i++)
                        {
                            //"[" + resultat.GetName(i) + "] : "
                            t[i] = FormatField(resultat, i);
                           
                        }
                        tmp.Add(t);
                        t = null;
                    } while (resultat.Read());
                

            }


                resultat.Close();

            return tmp;
        }

        public List<String[]> rechercheGlobaleGrid(string table, List<string> champ, List<string> value, string tri, int debut, int fin)
        {
            List<string[]> tmp = new List<string[]>();

            if (champ.Count > 0 && value.Count > 0)
            {
                string strRequete = "SELECT * FROM " + table + " WHERE " + champ[0] + " LIKE '" + value[0] + "%'";
                for (int i = 1; i < champ.Count; i++)
                    strRequete += " AND " + champ[i] + " LIKE '" + value[i] + "%'";

                strRequete += " ORDER BY " + tri; 

                Console.WriteLine(strRequete);

                try
                {

                    OdbcCommand commande = new OdbcCommand(strRequete, m_conn);

                    OdbcDataReader resultat = commande.ExecuteReader();
                    // On teste s'il existe un résultat
                    if (resultat.Read())
                    {
                        // Nombre de colonnes
                        int nbColonnes = resultat.FieldCount;
                        do
                        {
                            // Parcours des différentes colonnes
                            string[] t = new string[fin];
                            for (int i = 0; i < fin; i++)
                            {
                                //"[" + resultat.GetName(i) + "] : "
                                t[i] = FormatField(resultat, i);

                            }
                            tmp.Add(t);
                            t = null;
                        } while (resultat.Read());


                       
                    }

                    resultat.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }

            }
            return tmp;
        }

        public string SearchByIdBis(string table, string id, string value, int debut, int fin)
        {
            string strRequete = "SELECT * FROM " + table + " WHERE " + id + "=" + value + "";
            string t = "";

            OdbcCommand commande = new OdbcCommand(strRequete, m_conn);

            OdbcDataReader resultat = commande.ExecuteReader();

            if (resultat.Read())
            {

                for (int i = debut; i < fin; i++)
                    t += FormatField(resultat, i) + " ";


                
            }

            resultat.Close();

            return t;
        }

        public string[] SearchByIdGrid(string table, string id, string value, int debut, int fin)
        {
            string strRequete = "SELECT * FROM " + table + " WHERE " + id + "=" + value + "";
            int p = fin-debut;
            string[] t = new string[p];

            OdbcCommand commande = new OdbcCommand(strRequete, m_conn);

            OdbcDataReader resultat = commande.ExecuteReader();

            if (resultat.Read())
            {
                int j = 0;
                for (int i = debut; i < fin; i++) {
                    t[j] += FormatField(resultat, i) + " ";
                    j++;
                }


                
            }

            resultat.Close();
            return t;
        }

    }
}
